package com.pcbly.web.components.utils.excel;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.contrib.HSSFRegionUtil;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;

public class POIReportUtil {
	public static void setSheetName(HSSFWorkbook book, int index, String name) {
		book.setSheetName(index, name);
	}

	public static void setBorder(HSSFWorkbook workbook, HSSFSheet sheet,
			CellRangeAddress region, short border) {
		try {
			int bord = border;
			HSSFRegionUtil.setBorderBottom(bord, region, sheet, workbook);
			HSSFRegionUtil.setBorderLeft(bord, region, sheet, workbook);
			HSSFRegionUtil.setBorderRight(bord, region, sheet, workbook);
			HSSFRegionUtil.setBorderTop(bord, region, sheet, workbook);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static CellRangeAddress merge(HSSFSheet sheet, int srow, int scol,
			int erow, int ecol) {
		int i = sheet.addMergedRegion(new CellRangeAddress(srow, (short) scol,
				erow, (short) ecol));

		return sheet.getMergedRegion(i);
	}

	public static HSSFCell getCell(HSSFSheet sheet, int row, int col) {
		HSSFRow r = sheet.getRow(row);
		if (r == null) {
			r = sheet.createRow(row);
		}
		HSSFCell c = r.getCell(col);
		if (c == null) {
			c = r.createCell(col);
		}

		return c;
	}

	public static void copyStyle(HSSFSheet sheet, int fromRow, int fromCol,
			int toRow, int toCol) {
		HSSFCell from = getCell(sheet, fromRow, fromCol);
		HSSFCell to = getCell(sheet, toRow, toCol);
		to.setCellStyle(from.getCellStyle());
	}

	public static void updateCellValue(HSSFSheet sheet, int rowNum,
			short colNum, Object cellValue) {
		HSSFCell hssfCell = getCell(sheet, rowNum, colNum);
		if (cellValue instanceof Double)
			hssfCell.setCellValue(((Double) cellValue).doubleValue());
		else if (cellValue instanceof Boolean)
			hssfCell.setCellValue(((Boolean) cellValue).booleanValue());
		else if (cellValue instanceof Date)
			hssfCell.setCellValue(((Date) cellValue));
		else
			hssfCell.setCellValue(cellValue.toString());
	}

	public static void copyCell(HSSFSheet sheet, int fromRow, int fromCol,
			int toRow, int toCol, boolean copyStyle) {
		HSSFCell oldCell = getCell(sheet, fromRow, fromCol);
		HSSFCell newCell = getCell(sheet, toRow, toCol);
		copyCell(oldCell, newCell, copyStyle);
	}

	public static void writeToFile(HSSFWorkbook workbook, String fileName) {
		OutputStream os;
		try {
			os = new BufferedOutputStream(new FileOutputStream(fileName));
			workbook.write(os);
			os.flush();
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static HSSFWorkbook loadExcel(String fileName) {
		POIFSFileSystem fs;
		try {
			InputStream in = new BufferedInputStream(new FileInputStream(
					fileName), 4000);
			fs = new POIFSFileSystem(in);
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fs);
			return hssfWorkbook;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void copyCell(HSSFCell oldCell, HSSFCell newCell,
			boolean copyStyle) {
		if (copyStyle) {
			newCell.setCellStyle(oldCell.getCellStyle());
		}
		switch (oldCell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			newCell.setCellValue(oldCell.getStringCellValue());
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			newCell.setCellValue(oldCell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			newCell.setCellValue(oldCell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			newCell.setCellErrorValue(oldCell.getErrorCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			break;
		default:
			break;
		}
	}

	public static void copyCells(HSSFWorkbook workbook, HSSFSheet sheet,
			int startRow, int startCol, int endRow, int endCol,
			HSSFSheet destsheet, int toRow, short toCol) {
		for (int i = startRow, m = toRow; i <= endRow; i++, m++) {
			HSSFRow row = sheet.getRow(i);
			HSSFRow destrow = destsheet.getRow(m);
			for (int j = startCol, n = toCol; j <= endCol; j++, n++) {
				HSSFCell cell = row.getCell(j);
				if (cell == null) {
					cell = row.createCell(j);
				}
				HSSFCell destCell = destrow.getCell(n);
				if (destCell == null) {
					destCell = destrow.createCell(n);
				}
				copyCell(cell, destCell, true);

			}
		}
	}

	public static void clearCells(HSSFSheet sheet, int startRow, int startCol,
			int endRow, int endCol) {
		removeMergedRegion(sheet, startRow, startCol, endRow, endCol);
		for (int i = startRow; i <= endRow; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row != null) {
				for (int j = startCol; j <= endCol; j++) {
					HSSFCell cell = row.getCell(j);
					if (cell != null) {
						row.removeCell(cell);
					}
				}
			}
		}
	}

	public static boolean contains(CellRangeAddress r, int row, int col) {
		if (r.getFirstRow() <= row && row <= r.getLastRow()
				&& r.getFirstColumn() <= col && col <= r.getLastColumn())
			return true;
		else
			return false;
	}

	private static void printRegion(CellRangeAddress merged) {
		System.out.println(merged.getFirstRow() + "," + merged.getFirstColumn()
				+ "," + merged.getLastRow() + "," + merged.getLastColumn());
	}

	public static boolean removeMergedRegion(HSSFSheet sheet, int startRow,
			int startCol, int endRow, int endCol) {
		List<Integer> mergedRegionNumbersToRemove = new LinkedList<Integer>();
		CellRangeAddress range = new CellRangeAddress(startRow, startCol,
				endRow, endCol);

		for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
			CellRangeAddress merged = sheet.getMergedRegion(i);

			if (contains(merged, startRow, startCol)) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(merged, startRow, endCol)) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(merged, endRow, startCol)) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(merged, endRow, endCol)) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(range, merged.getFirstRow(), merged
					.getFirstColumn())) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(range, merged.getFirstRow(), merged
					.getLastColumn())) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(range, merged.getLastRow(), merged
					.getFirstColumn())) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			} else if (contains(range, merged.getLastRow(), merged
					.getLastColumn())) {
				mergedRegionNumbersToRemove.add(new Integer(i));
			}
		}
		for (Iterator<Integer> iterator = mergedRegionNumbersToRemove
				.iterator(); iterator.hasNext();) {
			Integer regionNumber = iterator.next();
			printRegion(sheet.getMergedRegion(regionNumber.intValue()));
			sheet.removeMergedRegion(regionNumber.intValue());
		}
		return !mergedRegionNumbersToRemove.isEmpty();
	}

	public static void shiftCellsLeft(HSSFSheet sheet, int startRow,
			int startCol, int endRow, int endCol, int shiftNumber) {
		for (int i = startRow; i <= endRow; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row != null) {
				for (int j = startCol; j <= endCol; j++) {
					HSSFCell cell = row.getCell(j);
					if (cell == null) {
						cell = row.createCell(j);
					}
					HSSFCell destCell = row.getCell((j - shiftNumber));
					if (destCell == null) {
						destCell = row.createCell((j - shiftNumber));
					}
					copyCell(cell, destCell, true);

					sheet.setColumnWidth(destCell.getColumnIndex(), sheet
							.getColumnWidth(cell.getColumnIndex()));
				}
			}
		}
	}

	public static void shiftCellsRight(HSSFSheet sheet, int startRow,
			int endRow, int startCol, int shiftNumber) {
		for (int i = startRow; i <= endRow; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row != null) {
				int lastCellNum = row.getLastCellNum();
				for (int j = lastCellNum; j >= startCol; j--) {
					HSSFCell destCell = row.getCell((j + shiftNumber));
					if (destCell == null) {
						destCell = row.createCell((j + shiftNumber));
					}
					HSSFCell cell = row.getCell(j);
					if (cell == null) {
						cell = row.createCell(j);
					}
					copyCell(cell, destCell, true);
				}
			}
		}
	}

	public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum,
			int cellNum) {
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress merged = sheet.getMergedRegion(i);

			if (merged.isInRange(rowNum, cellNum)) {
				return merged;
			}
		}
		return null;
	}

	public static HSSFCellStyle duplicateStyle(HSSFWorkbook workbook,
			HSSFCellStyle style) {
		HSSFCellStyle newStyle = workbook.createCellStyle();
		newStyle.setAlignment(style.getAlignment());
		newStyle.setBorderBottom(style.getBorderBottom());
		newStyle.setBorderLeft(style.getBorderLeft());
		newStyle.setBorderRight(style.getBorderRight());
		newStyle.setBorderTop(style.getBorderTop());
		newStyle.setBottomBorderColor(style.getBottomBorderColor());
		newStyle.setDataFormat(style.getDataFormat());
		newStyle.setFillBackgroundColor(style.getFillBackgroundColor());
		newStyle.setFillForegroundColor(style.getFillForegroundColor());
		newStyle.setFillPattern(style.getFillPattern());
		newStyle.setFont(workbook.getFontAt(style.getFontIndex()));
		newStyle.setHidden(style.getHidden());
		newStyle.setIndention(style.getIndention());
		newStyle.setLeftBorderColor(style.getLeftBorderColor());
		newStyle.setLocked(style.getLocked());
		newStyle.setRightBorderColor(style.getRightBorderColor());
		newStyle.setTopBorderColor(style.getTopBorderColor());
		newStyle.setVerticalAlignment(style.getVerticalAlignment());
		newStyle.setWrapText(style.getWrapText());
		return newStyle;
	}
}
